package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import util.JDBCUtil;
import domain.ForumCategory;
import ex.DatabaseException;

public class ForumCategoryDAO {

	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	
	private static final String SELECT_FORUMCATEGORY="select * from forum_category";
	private static final String SELECT_FORUMCATEGORY_BY_CID="select * from forum_category where cid = ?";
	private static final String EDIT_FORUMCATEGORY="update forum_category set name = ? where cid = ?";
	private static final String DELETE_FORUMCATEGORY="delete from forum_category where cid = ?";
	private static final String INSERT_FORUMCATEGORY="insert into forum_category (name) values (?)";
	
	//Selection of categories from ForumCategory table
	public LinkedList<ForumCategory> selectForumCategory() throws DatabaseException
	{
		conn = JDBCUtil.getConnection();
		LinkedList<ForumCategory> categories = new LinkedList<ForumCategory>();
		ForumCategory fc;
		try {
			stmt = conn.prepareStatement(SELECT_FORUMCATEGORY);
			rs = stmt.executeQuery();
			while (rs.next()) {
				fc = new ForumCategory(rs.getLong("cid"), rs.getString("name"));
				categories.add(fc);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}
		catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving category information! "	+ e.getMessage());
		}
		return categories;
	}
	
	//Selection of categories from ForumCategory table using Category ID
	public ForumCategory selectForumCategoryByCid(long cid) throws DatabaseException
	{
		conn = JDBCUtil.getConnection();
		ForumCategory categories = null;
		try {
			stmt = conn.prepareStatement(SELECT_FORUMCATEGORY_BY_CID);
			stmt.setLong(1, cid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String name = rs.getString("name");
				categories = new ForumCategory(cid, name);
			}
			
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}
		catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving category information! "	+ e.getMessage());
		}
		return categories;
	}
	
	//Editing the ForumCategory fields
	public void editForumCategory(ForumCategory forumCategory) throws DatabaseException {
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(EDIT_FORUMCATEGORY);
			stmt.setString(1, forumCategory.getName());
			stmt.setLong(2, forumCategory.getCid());
			stmt.execute();			
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with editing category information! "	+ e.getMessage());
		}
	}

	//Removal of a Category from the database
	public void deleteForumCategory(ForumCategory forumCategory) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(DELETE_FORUMCATEGORY);
			stmt.setLong(1, forumCategory.getCid());

			stmt.execute();		
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with deleting category information! "	+ e.getMessage());
		}
	}

	//Insertion of a new Category to the database
	public void insertForumCategory(ForumCategory forumCategory) throws DatabaseException {
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(INSERT_FORUMCATEGORY);
			stmt.setString(1, forumCategory.getName());
			
			stmt.execute();			
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with inserting category information! "	+ e.getMessage());
		}
	}
}
